﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace TestJQGrid
{
    public sealed class TMAdjustments
    {
        #region make it singleton - session based singleton
        public static TMAdjustments Instance
        {
            get
            {
                if (System.Web.HttpContext.Current.Session["AdjstmentScreen"] == null)
                    System.Web.HttpContext.Current.Session["AdjstmentScreen"] = new TMAdjustments();

                return (TMAdjustments)System.Web.HttpContext.Current.Session["AdjstmentScreen"];
            }
        }
        #endregion

        #region initialize - load/reload
        private DataTable datatable;
        private DataTable oldRows;
        private DataTable updatedRows;
        private int count = 0;

        TMAdjustments()
        {
            datatable = new DataTable();
            RetrieveData();
        }

        private void RetrieveData()
        {
            //retrieve data
            string selectQuery = "SELECT [TransId], [EntryStatus], [AgentId], [TransDate], [CarValue], [CityCode], [Model], ";
            selectQuery += " [Manufacturer], [Scheme], [Description], [IntRate], [CarType], [LoanAmount], [Tenure] FROM [CarSalesData] ";
            selectQuery += "WHERE ([EntryStatus] not in ('Adjusted') )";
            datatable = GlobalMethods.ExecuteSelectQuery(selectQuery);

            //set primary key
            DataColumn[] dc = new DataColumn[1];
            dc[0] = datatable.Columns["TransId"];
            datatable.PrimaryKey = dc;

            //reset newRows datatable
            oldRows = datatable.Clone();
            updatedRows = datatable.Clone();
        }

        public DataTable getData()
        {
            if (datatable == null)
                RetrieveData();
            return datatable;
        }

        public void reset()
        {
            oldRows = null;
            updatedRows = null;
            RetrieveData();
        }
        #endregion

        #region database update
        public void UpdateDB()
        {
            DataTable changedRows = datatable.GetChanges();

            foreach (DataRow dr in changedRows.Rows)
            {
                string status = dr[1].ToString();
                if ((status == "Deleted") || (status == "Adjusted"))
                {
                    string updateQuery = "UPDATE [CarSalesData] SET [EntryStatus] = '" + status + "' WHERE TransId =  '" + dr[0].ToString() + "'";
                    GlobalMethods.ExecuteNonQuery(updateQuery);
                }
                else if (status == "InAdjustment")
                {
                    string insertQuery = " INSERT INTO CarSalesData([EntryStatus], [AgentId], [TransDate], [CarValue], [CityCode],";
                    insertQuery += " [Model], [Manufacturer], [Scheme], [Description], [IntRate], [CarType], [LoanAmount], [Tenure] )";
                    insertQuery += " VALUES ('" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + Convert.ToDateTime(dr[3].ToString()).ToString("yyyy-MM-dd");
                    insertQuery += "','" + dr[4].ToString() + "','" + dr[5].ToString() + "','" + dr[6].ToString() + "','" + dr[7].ToString() + "','" + dr[8].ToString();
                    insertQuery += "','" + dr[9].ToString() + "','" + dr[10].ToString() + "','" + dr[11].ToString() + "','" + dr[12].ToString() + "','" + dr[13].ToString() + "')";

                    GlobalMethods.ExecuteIdentityInsert(insertQuery, "TransId");
                }
                else
                {
                    throw new Exception("Invalid Entry status while updating the entry");
                }
            }
        }

        public void deleteRow(int fieldId)
        {
            DataRow dr = datatable.Rows.Find(fieldId);
            dr[1] = "Deleted";
        }

        public void editRow(int fieldId, System.Collections.Specialized.NameValueCollection data)
        {
            DataRow dr = datatable.Rows.Find(fieldId);

            //save old entries ( change id for new entry: --count to dr )
            if (dr[1].ToString() != "InAdjustment") // add new rows only for first time
            {
                DataRow dr2 = datatable.NewRow();
                dr2.ItemArray = dr.ItemArray;
                dr2[1] = "Adjusted";
                dr[0] = --count; //old rows will keep original transId
                datatable.Rows.Add(dr2);
            }

            //add new entries
            dr[1] = "InAdjustment";
            dr[2] = data[0].ToString(); //agentid
            IFormatProvider culture = new System.Globalization.CultureInfo("fr-FR", true);
            dr[3] = Convert.ToDateTime(data[1].ToString(),culture); //trans date
            dr[6] = data[2].ToString(); //model
            dr[7] = data[3].ToString(); //manu
            dr[8] = data[4].ToString(); //scheme
            dr[12] = data[5].ToString(); //loan amount
        }
        #endregion
    }
}